#!/usr/bin/env/python
# import sip
# sip.setapi('QVariant', 2)

import os
# import sys
import sqlite3
import base64
import commonfunctions

class DataHandler():
    def __init__(self):
        self.common = commonfunctions.CommonFunctions()
        self.database = self.common.getAppPath() + "/data/wombatdata.db"
        self.defaultSections = [("request",), ("legal authority",), ("equipment",), ("evidence",), ("exam preparation",), ("examination",), ("evidence disposition",)]
        if not os.path.exists(self.database):
            # create empty file here
            tmpFile = open(self.database, "w+")
            tmpFile.close()
            self.common.displayDebug(self.database)
            # open newly created empty db file
            con = sqlite3.connect(self.database)
            # create default table's here
            con.execute("CREATE TABLE casetable(caseid INTEGER PRIMARY KEY, ucfn TEXT, uid TEXT, examinerlist TEXT)")
            con.execute("CREATE TABLE sectiontable(sectionid INTEGER PRIMARY KEY, sectiontitle TEXT)")
            con.execute("CREATE TABLE examinertable(examinerid INTEGER PRIMARY KEY, examinername TEXT)")
            con.execute("CREATE TABLE notetable(noteid INTEGER PRIMARY KEY, notecaption TEXT, notecontent TEXT, caseid INTEGER, sectionid INTEGER, ordernumber INTEGER, cdate DATETIME, mdate DATETIME)")
        else:
            # file exists, so just open a connection to it
            con = sqlite3.connect(self.database)
        # add the default sections to the sectiontable
        cur = con.cursor()
        cur.execute("SELECT COUNT(sectionid) FROM sectiontable")
        if cur.fetchone()[0] == 0:
            con.executemany("INSERT INTO sectiontable(sectiontitle) VALUES(?)", self.defaultSections)
        # commit all the execute's and close initial connection
        con.commit()
        con.close()

    def addNewCase(self, casefile):
        self.cursor.execute("INSERT INTO casetable (ucfn) VALUES(?)", (casefile, ))
        return self.cursor.lastrowid
    
    def caseExist(self, casefile):
        self.cursor.execute("SELECT caseid FROM casetable WHERE ucfn = (?)", (casefile, ))
        if self.cursor.fetchone() is None:
            return False
        else:
            return True

    def getCaseData(self, casefile):
        self.cursor.execute("SELECT * FROM casetable WHERE ucfn = (?)", (casefile, ))
        return self.cursor.fetchone()

    def getCaseFileList(self):
        tmplist = []
        self.cursor.execute("SELECT ucfn FROM casetable ORDER BY ucfn ASC")
        for row in self.cursor.fetchall():
            tmplist.append(row[0])
        return tmplist

    def getSections(self):
        con = sqlite3.connect(self.database)
        cur = con.cursor()
        cur.execute("SELECT * FROM sectiontable")
        return cur.fetchall()

    def casesExist(self):
        self.cursor.execute("SELECT COUNT(caseid) FROM casetable")
        if self.cursor.fetchone()[0] > 0:
            return True
        else:
            return False
    
    def insertNote(self, notecontent, notecaption, currentcaseid):
        self.cursor.execute("INSERT INTO notetable (notecontent, notecaption, caseid, notetype, cdate) VALUES(?, ?, ?, 1, datetime('now'))", (notecontent, notecaption, currentcaseid))

    def updateNoteContent(self, notecontent, notecaption, noteid):
        self.cursor.execute("UPDATE notetable SET notecontent = (?), notecaption = (?), mdate = datetime('now') WHERE noteid = (?)", (notecontent, notecaption, noteid))

    def updateNoteList(self, caseid):
        tmplist = []
        self.cursor.execute("SELECT notecaption FROM notetable where caseid = (?) and notetype = 1", (caseid, ))
        for row in self.cursor.fetchall():
            tmplist.append(row[0])
        return tmplist

    def getNoteValuesFromNoteList(self, notecaption, caseid):
        self.cursor.execute("SELECT notecontent,noteid FROM notetable WHERE notecaption = (?) AND caseid = (?) AND notetype = 1", (notecaption, caseid))
        return self.cursor.fetchone()

    def insertImage(self, filename, imagecaption, caseid):
        datastream = open(filename, "rb").read()
        self.cursor.execute("INSERT INTO notetable (notecontent, notecaption, caseid, notetype, cdate) VALUES(?, ?, ?, 2, datetime('now'))", (base64.encodestring(sqlite3.Binary(datastream)), imagecaption, caseid))

    def updateImageList(self, caseid):
        tmplist = []
        self.cursor.execute("SELECT notecaption FROM notetable WHERE caseid = (?) AND notetype = 2", (caseid, ))
        for row in self.cursor.fetchall():
            tmplist.append(row[0])
        return tmplist

    def getImageValuesFromImageList(self, imagecaption, caseid):
        self.cursor.execute("SELECT notecontent, noteid FROM notetable WHERE notecaption = (?) AND caseid = (?) AND notetype = 2", (imagecaption, caseid))
        rowdata = self.cursor.fetchone()
        tmpdata = base64.decodestring(rowdata[0])
        tmpid = rowdata[1]
        return tmpdata, tmpid

    def getImageDataFromImageId(self, imageid):
        self.cursor.execute("SELECT notecontent FROM notetable WHERE noteid = (?)", (imageid, ))
        rowdata = self.cursor.fetchone()
        tmpdata = base64.decodestring(rowdata[0])
        return tmpdata

    def updateImageData(self, imgdata, imageid):
        self.cursor.execute("UPDATE notetable SET notecontent = (?), mdate = datetime('now') WHERE noteid = (?)", (base64.encodestring(sqlite3.Binary(imgdata.getvalue())), imageid))

    def updateImageCaption(self, imageid, imagecaption):
        self.cursor.execute("UPDATE notetable SET notecaption = (?) WHERE noteid = (?)", (imagecaption, imageid))

    def deleteNote(self, noteid):
        self.cursor.execute("DELETE FROM notetable WHERE noteid = (?)", (noteid, ))

    def deleteImage(self, imageid):
        self.cursor.execute("DELETE FROM notetable WHERE noteid = (?)", (imageid, ))

    def insertTable(self, tablecaption, caseid):
        self.cursor.execute("INSERT INTO notetable (notecontent, notecaption, caseid, notetype, cdate) VALUES('::::::||::::::||::::::||::::::||::::::', ?, ?, 3, datetime('now'))", (tablecaption, caseid))

    def updateTableList(self, caseid):
        tmplist = []
        self.cursor.execute("SELECT notecaption FROM notetable WHERE caseid = (?) AND notetype = 3", (caseid, ))
        for row in self.cursor.fetchall():
            tmplist.append(row[0])
        return tmplist

    def getTableValuesFromTableList(self, tablecaption, caseid):
        self.cursor.execute("SELECT notecontent, noteid FROM notetable WHERE notecaption = (?) AND caseid = (?) AND notetype = 3", (tablecaption, caseid))
        tablelist = []
        tableid = -1
        tablelist[:] = []
        rowdata = self.cursor.fetchone()
        if rowdata is not None:
            tableid = rowdata[1]
            tablerows = rowdata[0].split("||")
            for row in tablerows:
                tablelist.append(row.split("::"))
        return tablelist, tableid

    def updateTableData(self, tabledata, tableid):
        self.cursor.execute("UPDATE notetable SET notecontent = (?), mdate = datetime('now') WHERE noteid = (?)", (tabledata, tableid))
    
    def updateTableCaption(self, tableid, tablecaption):
        self.cursor.execute("UPDATE notetable SET notecaption = (?) WHERE noteid = (?)", (tablecaption, tableid))

    def deleteTable(self, tableid):
        self.cursor.execute("DELETE FROM notetable WHERE noteid = (?)", (tableid, ))

    def upCaseSection(self, caseid, sectionid):
        tmpstring = ""
        tmplist = []
        newlist = []
        self.cursor.execute("SELECT sectionlist FROM casetable WHERE caseid = (?)", (caseid, ))
        rowdata = self.cursor.fetchone()
        if rowdata is not None:
            tmplist = rowdata[0].split("||")
            newlist = rowdata[0].split("||")
        for index in range(len(tmplist)):
            if int(tmplist[index]) == sectionid:
                newlist[index] = tmplist[index - 1]
                newlist[index - 1] = tmplist[index]
        for id in newlist:
            if len(tmpstring) > 0:
                tmpstring = tmpstring + "||" + id
            else:
                tmpstring = id
        self.cursor.execute("UPDATE casetable SET sectionlist = (?) WHERE caseid = (?)", (tmpstring, caseid))

    def downCaseSection(self, caseid, sectionid):
        tmpstring = ""
        tmplist = []
        newlist = []
        self.cursor.execute("SELECT sectionlist FROM casetable WHERE caseid = (?)", (caseid, ))
        rowdata = self.cursor.fetchone()
        if rowdata is not None:
            tmplist = rowdata[0].split("||")
            newlist = rowdata[0].split("||")
        for index in range(len(tmplist)):
            if int(tmplist[index]) == sectionid:
                newlist[index] = tmplist[index + 1]
                newlist[index + 1] = tmplist[index]
        for id in newlist:
            if len(tmpstring) > 0:
                tmpstring = tmpstring + "||" + id
            else:
                tmpstring = id
        self.cursor.execute("UPDATE casetable SET sectionlist = (?) WHERE caseid = (?)", (tmpstring, caseid))

    def upCaseExaminer(self, caseid, examinerid):
        tmpstring = ""
        tmplist = []
        newlist = []
        self.cursor.execute("SELECT examinerlist FROM casetable WHERE caseid = (?)", (caseid, ))
        rowdata = self.cursor.fetchone()
        if rowdata is not None:
            tmplist = rowdata[0].split("||")
            newlist = rowdata[0].split("||")
        for index in range(len(tmplist)):
            if int(tmplist[index]) == examinerid:
                newlist[index] = tmplist[index - 1]
                newlist[index - 1] = tmplist[index]
        for id in newlist:
            if len(tmpstring) > 0:
                tmpstring = tmpstring + "||" + id
            else:
                tmpstring = id
        self.cursor.execute("UPDATE casetable SET examinerlist = (?) WHERE caseid = (?)", (tmpstring, caseid))

    def downCaseExaminer(self, caseid, examinerid):
        tmpstring = ""
        tmplist = []
        newlist = []
        self.cursor.execute("SELECT examinerlist FROM casetable WHERE caseid = (?)", (caseid, ))
        rowdata = self.cursor.fetchone()
        if rowdata is not None:
            tmplist = rowdata[0].split("||")
            newlist = rowdata[0].split("||")
        for index in range(len(tmplist)):
            if int(tmplist[index]) == examinerid:
                newlist[index] = tmplist[index + 1]
                newlist[index + 1] = tmplist[index]
        for id in newlist:
            if len(tmpstring) > 0:
                tmpstring = tmpstring + "||" + id
            else:
                tmpstring = id
        self.cursor.execute("UPDATE casetable SET examinerlist = (?) WHERE caseid = (?)", (tmpstring, caseid))
    
    def getCurrentSectionId(self, sectiontitle):
        tmpid = -1
        self.cursor.execute("SELECT sectionid FROM sectiontable WHERE sectiontitle = (?)", (sectiontitle, ))
        rowdata = self.cursor.fetchone()
        if rowdata is not None:
            tmpid = rowdata[0]
        return tmpid

    def getCurrentExaminerId(self, examinername):
        tmpid = -1
        self.cursor.execute("SELECT examinerid FROM examinertable WHERE examinername = (?)", (examinername, ))
        rowdata = self.cursor.fetchone()
        if rowdata is not None:
            tmpid = rowdata[0]
        return tmpid
    
    def insertExaminer(self, examinername):
        self.cursor.execute("INSERT INTO examinertable (examinername) VALUES(?)", (examinername, ))

    def addCaseExaminer(self, caseid, examinerid):
        tmpstring = ""
        tmplist = []
        alreadyexists = 0
        self.cursor.execute("SELECT examinerlist FROM casetable WHERE caseid = (?)", (caseid, ))
        rowdata = self.cursor.fetchone()
        if rowdata is not None:
            if rowdata[0] is not None:
                tmpstring = rowdata[0]
                tmplist = rowdata[0].split("||")
            for id in tmplist:
                if id != '':
                    if int(id) == examinerid:
                        alreadyexists = 1
            if alreadyexists == 0:
                if len(tmpstring) > 0:
                    tmpstring = tmpstring + "||" + str(examinerid)
                else:
                    tmpstring = str(examinerid)
            self.cursor.execute("UPDATE casetable SET examinerlist = (?) WHERE caseid = (?)", (tmpstring, caseid))
    
    def insertSection(self, sectiontitle):
        self.cursor.execute("INSERT INTO sectiontable (sectiontitle) VALUES(?)", (sectiontitle, ))

    def addCaseSection(self, caseid, sectionid):
        tmpstring = ""
        tmplist = []
        alreadyexists = 0
        # get current sectionlist from case, then append this one to the end.
        self.cursor.execute("SELECT sectionlist FROM casetable WHERE caseid = (?)", (caseid, ))
        rowdata = self.cursor.fetchone()
        if rowdata is not None:
            if rowdata[0] is not None:
                tmpstring = rowdata[0]
                tmplist = rowdata[0].split("||")
        for id in tmplist:
            if int(id) == sectionid:
                alreadyexists = 1
        if alreadyexists == 0:
            if len(tmpstring) > 0:
                tmpstring = tmpstring + "||" + str(sectionid)
            else:
                tmpstring = str(sectionid)
            self.cursor.execute("UPDATE casetable SET sectionlist = (?) WHERE caseid = (?)", (tmpstring, caseid))

    def removeCaseExaminer(self, caseid, examinerid):
        tmplist = []
        tmpstring = ""
        self.cursor.execute("SELECT examinerlist FROM casetable WHERE caseid = (?)", (caseid, ))
        rowdata = self.cursor.fetchone()
        if rowdata is not None:
            tmplist = rowdata[0].split("||")
        for id in tmplist:
            if int(id) != examinerid:
                if len(tmpstring) > 0:
                    tmpstring = tmpstring + "||" + id
                else:
                    tmpstring = id
        self.cursor.execute("UPDATE casetable SET examinerlist = (?) WHERE caseid = (?)", (tmpstring, caseid))

    def removeCaseSection(self, caseid, sectionid):
        tmplist = []
        tmpstring = ""
        self.cursor.execute("SELECT sectionlist FROM casetable WHERE caseid = (?)", (caseid, ))
        rowdata = self.cursor.fetchone()
        if rowdata is not None:
            tmplist = rowdata[0].split("||")
        for id in tmplist:
            if int(id) != sectionid:
                if len(tmpstring) > 0:
                    tmpstring = tmpstring + "||" + id
                else:
                    tmpstring = id
        self.cursor.execute("UPDATE casetable SET sectionlist = (?) WHERE caseid = (?)", (tmpstring, caseid))

    def updateSectionList(self, caseid):
        idlist = []
        tmplist = []
        sectioncount = 0
        # get current sectionid list, get sectiontitles, then compare and add to items
        self.cursor.execute("SELECT sectionlist FROM casetable WHERE caseid = (?)", (caseid, ))
        rowdata = self.cursor.fetchone()
        if rowdata is not None:
            if rowdata[0] is not None:
                idlist = rowdata[0].split("||")
        self.cursor.execute("SELECT sectionid, sectiontitle FROM sectiontable")
        rowdata = self.cursor.fetchall()
        for id in idlist:
            for row in rowdata:
                if int(id) == row[0]:
                    tmplist.append(row[1])
        self.common.removeduplicates(tmplist)
        return tmplist

    def updateAvailableSectionList(self):
        tmplist = []
        self.cursor.execute("SELECT sectiontitle from sectiontable")
        rowdata = self.cursor.fetchall()
        for row in rowdata:
            tmplist.append(row[0])
        return tmplist

    def updateCaseExaminerList(self, caseid):
        idlist = []
        tmplist = []
        self.cursor.execute("SELECT examinerlist FROM casetable WHERE caseid = (?)", (caseid, ))
        rowdata = self.cursor.fetchone()
        if rowdata is not None:
            if rowdata[0] is not None:
                idlist = rowdata[0].split("||")
        self.cursor.execute("SELECT examinerid, examinername FROM examinertable")
        rowdata = self.cursor.fetchall()
        if rowdata is not None:
            for id in idlist:
                for row in rowdata:
                    if id !=  '':
                        if int(id) == row[0]:
                            tmplist.append(row[1])
        self.common.removeduplicates(tmplist)
        return tmplist

    def updateExaminerList(self):
        tmplist = []
        self.cursor.execute("SELECT examinername from examinertable")
        rowdata = self.cursor.fetchall()
        if rowdata is not None:
            for row in rowdata:
                tmplist.append(row[0])
        return tmplist

    def upSectionNote(self, caseid, sectionid, noteid):
        tmplist = []
        newlist = []
        a = 1
        self.cursor.execute("SELECT noteid FROM notetable WHERE caseid = (?) AND sectionid = (?) ORDER BY ordernumber ASC", (caseid, sectionid))
        rowdata = self.cursor.fetchall()
        if rowdata is not None:
            for row in rowdata:
                tmplist.append(row[0])
                newlist.append(row[0])
            for index in range(len(tmplist)):
                if tmplist[index] == noteid:
                    newlist[index] = tmplist[index - 1]
                    newlist[index - 1] = tmplist[index]
            for id in newlist:
                self.cursor.execute("UPDATE notetable SET ordernumber = (?) WHERE noteid = (?)", (a, id))
                a = a + 1

    def downSectionNote(self, caseid, sectionid, noteid):
        tmplist = []
        newlist = []
        a = 1
        self.cursor.execute("SELECT noteid FROM notetable WHERE caseid = (?) AND sectionid = (?) ORDER BY ordernumber ASC", (caseid, sectionid))
        rowdata = self.cursor.fetchall()
        if rowdata is not None:
            for row in rowdata:
                tmplist.append(row[0])
                newlist.append(row[0])
            for index in range(len(tmplist)):
                if tmplist[index] == noteid:
                    newlist[index] = tmplist[index + 1]
                    newlist[index + 1] = tmplist[index]
            for id in newlist:
                self.cursor.execute("UPDATE notetable SET ordernumber = (?) WHERE noteid = (?)", (a, id))
                a = a + 1

    def reorderSectionNotes(self, caseid, sectionid):
        tmplist = []
        a = 1
        self.cursor.execute("SELECT noteid FROM notetable WHERE caseid = (?) AND sectionid = (?) ORDER BY ordernumber ASC", (caseid, sectionid))
        rowdata = self.cursor.fetchall()
        if rowdata is not None:
            for row in rowdata:
                tmplist.append(row[0])
            for id in tmplist:
                self.cursor.execute("UPDATE notetable SET ordernumber = (?) WHERE noteid = (?)", (a, id))
                a = a + 1

    def delSectionNote(self, noteid):
        self.cursor.execute("UPDATE notetable SET sectionid = NULL, ordernumber = NULL WHERE noteid = (?)", (noteid, )) 

    def addSectionNote(self, sectionid, noteid, orderentry):
        self.cursor.execute("UPDATE notetable SET sectionid = (?), ordernumber = (?) WHERE noteid = (?)", (sectionid, orderentry, noteid))

    def getLastOrderNumber(self, caseid, sectionid):
        self.cursor.execute("SELECT ordernumber FROM notetable WHERE caseid = (?) AND sectionid = (?) ORDER BY ordernumber DESC", (caseid, sectionid))
        rowdata = self.cursor.fetchone()
        if rowdata is not None:
            return rowdata[0]
        else:
            return 1
    
    def getCurrentNoteId(self, notecaption):
        self.cursor.execute("SELECT noteid FROM notetable WHERE notecaption = (?)", (notecaption, ))
        rowdata = self.cursor.fetchone()
        if rowdata is not None:
            return rowdata[0]
        else:
            return -1

    def updateUsedNoteList(self, caseid, sectionid):
        tmplist = []
        self.cursor.execute("SELECT notecaption FROM notetable WHERE caseid = (?) AND sectionid = (?) ORDER BY ordernumber ASC", (caseid, sectionid))
        rowdata = self.cursor.fetchall()
        if rowdata is not None:
            for row in rowdata:
                tmplist.append(row[0])
        return tmplist

    def updateUnusedNoteList(self, caseid):
        tmplist = []
        self.cursor.execute("SELECT notecaption FROM notetable WHERE caseid = (?) AND ordernumber IS NULL", (caseid, ))
        rowdata = self.cursor.fetchall()
        if rowdata is not None:
            for row in rowdata:
                tmplist.append(row[0])
        return tmplist

    def getCaseSettings(self, caseid):
        tmpucfn = ""
        tmpuid = ""
        self.cursor.execute("SELECT ucfn, uid FROM casetable WHERE caseid = (?)", (caseid, ))
        rowdata = self.cursor.fetchone()
        if rowdata is not None:
            if rowdata[0] is not None:
                tmpucfn = rowdata[0]
            if rowdata[1] is not None:
                tmpuid = rowdata[1]
        return tmpucfn, tmpuid

    def updateCaseSettings(self, caseid, ucfn, uid):
        self.cursor.execute("UPDATE casetable SET ucfn = (?), uid = (?) WHERE caseid = (?)", (ucfn, uid, caseid))
    
    def sql(self, tmpFunction, tmpinputlist, hasreturn): # function, inputarray[,], whether there is a return value
        tmpreturn = None
        self.opensql()
        if hasreturn == 1:
            if tmpinputlist is None:
                tmpreturn = tmpFunction()
            else:
                tmpreturn = tmpFunction(*tmpinputlist)
        else:
            if tmpinputlist is None:
                tmpFunction()
            else:
                tmpFunction(*tmpinputlist)
        self.commitsql()
        self.closesql()
        if hasreturn == 1:
            return tmpreturn

    def opensql(self):
        self.con = sqlite3.connect(self.database)
        self.cursor = self.con.cursor()
    
    def select(self, query):
        self.cursor.execute(query)

    def execute(self, query, values):
        self.cursor.executemany(query, values)

    def fetchone(self):
        return self.cursor.fetchone()

    def fetchall(self):
        return self.cursor.fetchall()

    def commitsql(self):
        self.con.commit()

    def returnLastRowId(self):
        return self.cursor.lastrowid

    def closesql(self):
        self.con.close()
